﻿create PROCEDURE Sync.SP_ProjectStages_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @ProjectStages_Temp TABLE 
		(
			ProjectStageID UniqueIdentifier,
			[ProjectID] uniqueidentifier,
				[Stage] int,
				[AgeLimit] int,
				[From] float,
				[To] float,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @ProjectStages_Temp 
	   SELECT	Tbl.Col.value('ProjectStageID[1]','uniqueidentifier') ProjectStageID,
                Tbl.Col.value('ProjectID[1]', 'uniqueidentifier') [ProjectID],
Tbl.Col.value('Stage[1]', 'int') [Stage],
Tbl.Col.value('AgeLimit[1]', 'int') [AgeLimit],
Tbl.Col.value('From[1]', 'float') [From],
Tbl.Col.value('To[1]', 'float') [To],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/ProjectStages') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[ProjectStages] ([ProjectStageID],[ProjectID],[Stage],[AgeLimit],[From],[To])
	SELECT t.[ProjectStageID],t.[ProjectID],t.[Stage],t.[AgeLimit],t.[From],t.[To]
	FROM  @ProjectStages_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.ProjectStages_Tracking s
        WHERE t.ProjectStageID = s.ProjectStageID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[ProjectStages]
	SET [ProjectStages].[ProjectID] = t.[ProjectID],[ProjectStages].[Stage] = t.[Stage],[ProjectStages].[AgeLimit] = t.[AgeLimit],[ProjectStages].[From] = t.[From],[ProjectStages].[To] = t.[To] 
	FROM [dbo].[ProjectStages] s JOIN @ProjectStages_Temp t 
    ON t.ProjectStageID = s.ProjectStageID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.ProjectStages_Tracking r
				  WHERE t.ProjectStageID = r.ProjectStageID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[ProjectStages]
    FROM @ProjectStages_Temp t JOIN [dbo].[ProjectStages] s
    ON t.ProjectStageID = s.ProjectStageID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END














